package com.data.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.base.dao.TemplateDaoImpl;
import com.data.entity.ChargeDay;
@Repository("chargeDayDao")
public class ChargeDayDaoImpl extends TemplateDaoImpl<ChargeDay, Integer>implements ChargeDayDao{
	/**
	 * 根据设备id和归属日期查询电费每天记录
	 * @param id 设备或者生产线id
	 * @param belongDate
	 * @param chargeName
	 * @param isLine
	 * @return
	 */
	@Override
	public ChargeDay getChargeDayByDate(int id,String chargeName, String belongDate,String isLine) {
			StringBuilder sb=new StringBuilder("select * from chargeDay where ");
			if(isLine.equals("Y")){
				sb.append(" lineId=:id ");
			}else{
				sb.append(" equId=:id ");
			}
			sb.append(" and chargeName=:chargeName  and CONVERT(VARCHAR(10),belongDate,120)=:belongDate");
//			String sql="select * from chargeDay where equId=:equId and chargeName=:chargeName  and CONVERT(VARCHAR(10),belongDate,120)=:belongDate";
			Map<String, Object> params=new HashMap<String, Object>();
			params.put("id", id);
			params.put("chargeName", chargeName);
			params.put("belongDate", belongDate);
			return jt.queryForObject(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeDay.class));	
	}
	/**
	 * 根据开始日期结束日期查询电费区间记录
	 * @param id生产线或者设备id
	 * @param startDate
	 * @param endDate
	 * @param chargeName
	 * @param isLine
	 * @return
	 */
	@Override
	public List<ChargeDay> getChargeDaysByDate(int id, String startDate,String endDate,String chargeName,String isLine) {
		StringBuilder sb=new StringBuilder("select * from chargeDay where ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		sb.append("  and CONVERT(VARCHAR(10),belongDate,120)>=:startDate and CONVERT(VARCHAR(10),belongDate,120)<=:endDate and chargeName=:chargeName");
		
//		String  sql="select * from chargeDay where equId=:equId and CONVERT(VARCHAR(10),belongDate,120)>=:startDate and CONVERT(VARCHAR(10),belongDate,120)<=:endDate and chargeName=:chargeName";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		params.put("chargeName", chargeName);
		return jt.query(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeDay.class));
	}
	
	/**
	 * 根据日期和equid找到所有记录
	 * @param equId
	 * @param belongDate
	 * @return
	 */
	@Override
	public List<ChargeDay> getChargeDayIdAndDate(int id, String belongDate,String isLine) {
//		String  sql="select * from chargeDay where equId=:equId and CONVERT(VARCHAR(10),belongDate,120)=:belongDate ";
		StringBuffer sb=new StringBuffer("select * from chargeDay where");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		sb.append(" and CONVERT(VARCHAR(10),belongDate,120)=:belongDate");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		params.put("belongDate", belongDate);
		return jt.query(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeDay.class));
	}
	
	//1.查询日期区间里所有的chargeName记录
	@Override
	public List<ChargeDay> getDistricChargeName(int id, String startDate,
			String endDate, String isLine) {
		StringBuffer sb=new StringBuffer("select distinct(chargeName) chargeName from chargeDay  where ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		sb.append(" and CONVERT(VARCHAR(10),belongDate,120)>=:startDate and CONVERT(VARCHAR(10),belongDate,120)<=:endDate");
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		return jt.query(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeDay.class));
	}
	
	//2.查询日期区间里对应类型的电度电费累加记录
	@Override
	public ChargeDay getCompAndChargeByType(int id, String startDate,
			String endDate, String chargeName, String isLine) {
		Map<String, Object> params=new HashMap<String, Object>();
		StringBuffer sb=new StringBuffer("select  COALESCE(SUM(relativeComp),0)relativeComp, COALESCE(SUM(charge),0) charge  from chargeDay  where ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		if(chargeName!=null&&!chargeName.equals("")){
			sb.append(" and chargeName=:chargeName");
			params.put("chargeName", chargeName);
		}
		sb.append(" and CONVERT(VARCHAR(10),belongDate,120)>=:startDate and CONVERT(VARCHAR(10),belongDate,120)<=:endDate");
		params.put("id", id);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		return jt.queryForObject(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeDay.class));	
	}

}
